==============  EXO 1 ======================

SELECT * FROM parc_informatique WHERE systeme="Windows XP";
SELECT  batiment, COUNT(poste) AS postes_sous_XP FROM parc_informatique WHERE systeme="Windows XP" GROUP BY batiment;
SELECT nom,age FROM zoo WHERE sexe=='M';


============  EXO 2  =======================

SELECT DISTINCT  lycee_origine FROM Eleves2018;
SELECT DISTINCT  prenom FROM Eleves2018 WHERE numero==1;
SELECT prenom, nom  FROM Eleves2018 WHERE note_bac<14;
SELECT lycee_origine, COUNT(nom)  as nombre_eleves FROM Eleves2018 GROUP BY lycee_origine;
SELECT 'BCPST' || numero AS classe, AVG(note_bac)  AS moyenne FROM Eleves2018 GROUP BY numero;


============  EXO 3  =====================

SELECT client FROM nuitee JOIN lit ON lit==id_lit WHERE bat_lit=='Rose';
SELECT client FROM nuitee JOIN lit JOIN batiment ON lit==id_lit AND bat_lit==nom WHERE etoiles==3;
SELECT client FROM nuitee JOIN lit JOIN chambre ON lit==id_lit AND chambre==numero AND bat_lit==batiment WHERE fenetres>=2;


============ EXO 4 =======================

SELECT SUM(population_1999) AS pop_1999, SUM(population_2010) AS pop_2010 FROM communes ;
SELECT COUNT(nom) AS noms, COUNT(DISTINCT nom) AS noms_distincts FROM communes ;
SELECT COUNT(*) AS En_Bretagne FROM communes WHERE num_departement IN (35,56,29,22);

Avec jointure :
SELECT COUNT(*) FROM communes c JOIN departements d ON  c.num_departement ==d.num_departement WHERE num_region==6;


============ EXO 5 =====================

SELECT  nom,num_departement FROM communes ORDER BY surface  LIMIT 20 ;
SELECT  nom,num_departement,population_2010 FROM communes ORDER BY population_2010  DESC LIMIT 20 ;
SELECT  nom, population_2010/surface as densite FROM communes WHERE num_departement==35 ORDER BY densite  DESC LIMIT 20 ;


========= EXO 6 ===================

SELECT COUNT(*) FROM  communes WHERE nom LIKE '%x%';
SELECT COUNT(*) FROM  communes WHERE nom LIKE "%a%" AND nom LIKE "%e%"  AND nom LIKE "%i%" AND nom LIKE "%o%"AND nom LIKE "%u%" AND nom LIKE "%y%"  ;
SELECT nom FROM  communes WHERE nom LIKE "%a%" AND nom LIKE "%e%"  AND nom LIKE "%i%" AND nom LIKE "%o%"AND nom LIKE "%u%" AND nom LIKE "%y%" AND num_departement==56  ;


========= EXO 7 ====================

SELECT nom,population_2010-population_1999 AS augmentation FROM  communes WHERE num_departement==44 ORDER BY augmentation DESC LIMIT 5;
SELECT nom, 100*(population_2010-population_1999)/population_1999 AS augmentation FROM  communes WHERE num_departement==44 ORDER BY augmentation DESC LIMIT 5;


========= EXO 8 =====================

Avec les numéros de départements : 

SELECT  num_departement,COUNT(*) AS nombre FROM communes GROUP BY num_departement ORDER BY nombre DESC  ;
SELECT  num_departement,SUM(population_2010) AS population FROM communes GROUP BY num_departement ORDER BY population DESC  ;
SELECT  num_departement,SUM(surface) AS superficie FROM communes GROUP BY num_departement ORDER BY superficie DESC  ;


Avec une jointure, on obtient les noms :
SELECT  d.nom,COUNT(*) AS nombre FROM communes c JOIN departements d ON c.num_departement==d.num_departement GROUP BY d.nom ORDER BY nombre DESC  ;
SELECT  d.nom,SUM(population_2010) AS population  FROM communes c JOIN departements d ON c.num_departement==d.num_departement GROUP BY d.nom ORDER BY population  DESC  ;
SELECT  d.nom,SUM(surface) AS superficie  FROM communes c JOIN departements d ON c.num_departement==d.num_departement GROUP BY d.nom ORDER BY superficie  DESC  ;
SELECT  d.nom,SUM(population_2010)/SUM(surface) AS densite  FROM communes c JOIN departements d ON c.num_departement==d.num_departement GROUP BY d.nom ORDER BY densite  DESC  ;


=========  EXO 9  ====================

SELECT r.nom AS region,d.nom AS departement FROM  departements d JOIN regions r ON  d.num_region==r.num_region ORDER BY r.nom,d.nom ;
SELECT  d.nom AS departement, MAX(c.population_2010)  AS pop_max FROM  departements d JOIN communes c ON  d.num_departement==c.num_departement GROUP BY c.num_departement ORDER BY pop_max ;



========= EXO 10 =====================


SELECT r.nom, COUNT(*) AS dpts FROM  departements d JOIN regions r 
ON  d.num_region==r.num_region GROUP BY r.nom ORDER BY dpts DESC;

SELECT r.nom, SUM(population_2010) AS population FROM communes c JOIN departements d JOIN regions r 
ON c.num_departement==d.num_departement AND d.num_region==r.num_region GROUP BY r.nom ORDER BY population DESC;

SELECT r.nom, SUM(surface) AS superficie FROM communes c JOIN departements d JOIN regions r 
ON c.num_departement==d.num_departement AND d.num_region==r.num_region GROUP BY r.nom ORDER BY superficie DESC;

SELECT r.nom, SUM(population_2010)/SUM(surface) AS densite FROM communes c JOIN departements d JOIN regions r 
ON c.num_departement==d.num_departement AND d.num_region==r.num_region GROUP BY r.nom ORDER BY densite DESC;